"""
工单管理CRUD操作
包含工单和告警工单关联的增删改查操作
"""
from datetime import datetime
from typing import Optional, Tuple, List

from app.models.work_order_models import WorkOrder, WorkOrderLog
from app.schemas.work_order_schemas import (
	WorkOrderCreate, WorkOrderUpdate, WorkOrderQuery,
	WorkOrderDispatchRequest, WorkOrderResolveRequest, WorkOrderCloseRequest,
	WorkOrderProcessRequest
)
from common.base_enums import WorkOrderStatus, WorkOrderAction
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger(__name__)


# ---------- 工单主表操作 ----------

async def get_work_order_by_id(db: DatabaseService, work_order_id: int) -> Optional[WorkOrder]:
	"""
	根据ID获取工单

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID

	Returns:
		Optional[WorkOrder]: 工单信息，不存在则返回None
	"""
	sql = """
        SELECT id, code, title, source_type, alarm_id, device_type, device_id, 
               priority, description, expect_finish_time, assignee_code, status,
               solution, close_comment, closed_by, closed_at, ext, deleted,
               created_at, updated_at
        FROM wo_work_order
        WHERE id = %s AND deleted = 0
    """
	try:
		row = db.get_one(sql, (work_order_id,))
		if not row:
			return None
		return WorkOrder(**row)
	except Exception as e:
		logger.error(f"查询工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message=f"查询工单失败: {str(e)}")


async def get_work_order_list(db: DatabaseService, query: WorkOrderQuery) -> Tuple[int, List[WorkOrder]]:
	"""
	获取工单列表（分页）

	Args:
		db: 数据库服务实例
		query: 查询参数

	Returns:
		Tuple[int, List[WorkOrder]]: 总数和工单列表
	"""
	where_conditions = ["wo.deleted = 0"]
	params = []

	if query.status:
		if len(query.status) == 1:
			where_conditions.append("wo.status = %s")
			params.append(query.status[0].value)
		else:
			placeholders = ", ".join(["%s"] * len(query.status))
			where_conditions.append(f"wo.status IN ({placeholders})")
			params.extend([s.value for s in query.status])

	if query.start_time:
		where_conditions.append("wo.created_at >= %s")
		params.append(query.start_time)

	if query.end_time:
		where_conditions.append("wo.created_at <= %s")
		params.append(query.end_time)

	if query.assignee_code:
		where_conditions.append("wo.assignee_code = %s")
		params.append(query.assignee_code)

	if query.device_id:
		where_conditions.append("wo.device_id = %s")
		params.append(query.device_id)

	if query.station_id:
		where_conditions.append("v.station_id = %s")
		params.append(query.station_id)

	if query.source_type:
		where_conditions.append("wo.source_type = %s")
		params.append(query.source_type.value)

	where_clause = " AND ".join(where_conditions)
	if where_clause:
		where_clause = f"WHERE {where_clause}"

	count_sql = f"""
		SELECT 
		  COUNT(*) as count 
		FROM wo_work_order wo 
		LEFT JOIN am_alarm am ON am.id = wo.alarm_id
		LEFT JOIN v_all_device v on am.meter_code = v.serial_no
		{where_clause}
		"""

	try:
		count_row = db.get_one(count_sql, params)
		total = count_row["count"] if count_row else 0

		if total == 0:
			return 0, []

		offset = (query.page - 1) * query.size
		select_sql = f"""
            SELECT wo.id, wo.code, wo.title, wo.source_type, wo.alarm_id, wo.device_type, wo.device_id, 
				   wo.priority, wo.description, wo.expect_finish_time, wo.assignee_code, wo.status,
				   wo.solution, wo.close_comment, wo.closed_by, wo.closed_at, wo.ext, wo.deleted,
				   wo.created_at, wo.updated_at
			FROM wo_work_order wo 
			LEFT JOIN am_alarm am ON am.id = wo.alarm_id
			LEFT JOIN v_all_device v on am.meter_code = v.serial_no
			{where_clause}
			ORDER BY wo.created_at DESC
            LIMIT %s OFFSET %s
        """
		params.extend([query.size, offset])
		rows = db.get_all(select_sql, params)

		work_orders = [WorkOrder(**row) for row in rows]
		return total, work_orders
	except Exception as e:
		logger.error(f"查询工单列表失败: {e}")
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message=f"查询工单列表失败: {str(e)}")


async def create_work_order(db: DatabaseService, payload: WorkOrderCreate, creator_code: str) -> int:
	"""
	创建工单

	Args:
		db: 数据库服务实例
		payload: 工单创建数据
		creator_code: 创建人编码

	Returns:
		int: 新创建的工单ID
	"""
	# 生成工单编码
	today = datetime.now().strftime("%Y%m%d")
	# 先查询当天最大流水号
	seq_sql = """
        SELECT MAX(CAST(SUBSTRING(code, 10) AS UNSIGNED)) as max_seq 
        FROM wo_work_order 
        WHERE code LIKE %s AND deleted = 0
    """
	seq_row = db.get_one(seq_sql, (f"W{today}%",))
	max_seq = seq_row["max_seq"] if seq_row and seq_row["max_seq"] else 0
	seq = str(max_seq + 1).zfill(4)
	code = f"W{today}{seq}"

	sql = """
        INSERT INTO wo_work_order (
            code, title, source_type, alarm_id, device_type, device_id,
            priority, description, status, assignee_code, expect_finish_time,
            ext, deleted, created_at, updated_at
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW()
        )
    """
	try:
		params = (
			code,
			payload.title,
			payload.source_type.value,
			payload.alarm_id,
			payload.device_type.value if payload.device_type else None,
			payload.device_id,
			payload.priority.value,
			payload.description,
			WorkOrderStatus.CREATED.value,
			None,  # assignee_code
			None,  # expect_finish_time
			None,  # ext
			0,  # deleted
		)
		work_order_id = db.insert(sql, params)

		# 记录创建日志
		await create_work_order_log(
			db, work_order_id, WorkOrderAction.CREATE, creator_code,
			None, WorkOrderStatus.CREATED, "创建工单"
		)

		return work_order_id
	except Exception as e:
		logger.error(f"创建工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"创建工单失败: {str(e)}")


async def update_work_order(
		db: DatabaseService,
		work_order_id: int,
		payload: WorkOrderUpdate,
		operator_code: str
) -> bool:
	"""
	更新工单

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID
		payload: 工单更新数据
		operator_code: 操作人编码

	Returns:
		bool: 更新成功返回True，工单不存在返回False
	"""
	# 检查工单是否存在
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return False

	# 构建更新语句
	update_fields = []
	params = []

	if payload.title is not None:
		update_fields.append("title = %s")
		params.append(payload.title)

	if payload.priority is not None:
		update_fields.append("priority = %s")
		params.append(payload.priority.value)

	if payload.description is not None:
		update_fields.append("description = %s")
		params.append(payload.description)

	if not update_fields:
		return True  # 没有需要更新的字段

	params.append(work_order_id)
	sql = f"UPDATE wo_work_order SET {', '.join(update_fields)}, updated_at = NOW() WHERE id = %s"

	try:
		affected_rows = db.update(sql, params)

		# 记录更新日志
		if affected_rows > 0:
			await create_work_order_log(
				db, work_order_id, WorkOrderAction.CREATE, operator_code,
				None, work_order.status, f"更新工单信息"
			)

		return affected_rows > 0
	except Exception as e:
		logger.error(f"更新工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"更新工单失败: {str(e)}")


async def delete_work_order(db: DatabaseService, work_order_id: int, operator_code: str) -> bool:
	"""
	删除工单（逻辑删除）

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID
		operator_code: 操作人编码

	Returns:
		bool: 删除成功返回True，工单不存在返回False
	"""
	# 检查工单是否存在
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return False

	sql = "UPDATE wo_work_order SET deleted = 1, updated_at = NOW() WHERE id = %s"
	try:
		affected_rows = db.update(sql, (work_order_id,))

		# 记录删除日志
		if affected_rows > 0:
			await create_work_order_log(
				db, work_order_id, WorkOrderAction.CREATE, operator_code,
				work_order.status, None, "删除工单"
			)

		return affected_rows > 0
	except Exception as e:
		logger.error(f"删除工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"删除工单失败: {str(e)}")


# ---------- 工单状态操作 ----------

async def update_work_order_status(
		db: DatabaseService,
		work_order_id: int,
		new_status: WorkOrderStatus,
		operator_code: str,
		comment: Optional[str] = None
) -> bool:
	"""
	更新工单状态

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID
		new_status: 新状态
		operator_code: 操作人编码
		comment: 备注

	Returns:
		bool: 更新成功返回True，工单不存在返回False
	"""
	# 检查工单是否存在
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return False

	# 检查状态流转是否合法
	if not _is_valid_status_transition(work_order.status, new_status):
		raise BizException.from_error_code(
			ErrorCode.BAD_REQUEST,
			message=f"状态流转不合法: {work_order.status.label()} → {new_status.label()}"
		)

	sql = "UPDATE wo_work_order SET status = %s, updated_at = NOW() WHERE id = %s"
	try:
		affected_rows = db.update(sql, (new_status.value, work_order_id))

		# 记录状态变更日志
		if affected_rows > 0:
			action = _get_action_by_status_transition(work_order.status, new_status)
			await create_work_order_log(
				db, work_order_id, action, operator_code,
				work_order.status, new_status,
				comment or f"状态变更: {work_order.status.label()} → {new_status.label()}"
			)

		return affected_rows > 0
	except Exception as e:
		logger.error(f"更新工单状态失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"更新工单状态失败: {str(e)}")


def _is_valid_status_transition(old_status: WorkOrderStatus, new_status: WorkOrderStatus) -> bool:
	"""
	检查工单状态流转是否合法

	Args:
		old_status: 原状态
		new_status: 新状态

	Returns:
		bool: 合法返回True，否则返回False
	"""
	# 定义合法的状态流转
	valid_transitions = {
		WorkOrderStatus.CREATED: [WorkOrderStatus.DISPATCHED],
		WorkOrderStatus.DISPATCHED: [WorkOrderStatus.PROCESSING],
		WorkOrderStatus.PROCESSING: [WorkOrderStatus.RESOLVED],
		WorkOrderStatus.RESOLVED: [WorkOrderStatus.CLOSED],
		WorkOrderStatus.CLOSED: [],  # 终态，不能再变更
	}

	return new_status in valid_transitions.get(old_status, [])


def _get_action_by_status_transition(old_status: WorkOrderStatus, new_status: WorkOrderStatus) -> WorkOrderAction:
	"""
	根据状态流转获取对应的操作类型

	Args:
		old_status: 原状态
		new_status: 新状态

	Returns:
		WorkOrderAction: 操作类型
	"""
	transition_map: dict[tuple[WorkOrderStatus, WorkOrderStatus], WorkOrderAction] = {
		(WorkOrderStatus.CREATED, WorkOrderStatus.DISPATCHED): WorkOrderAction.DISPATCH,
		(WorkOrderStatus.DISPATCHED, WorkOrderStatus.PROCESSING): WorkOrderAction.PROCESS,
		(WorkOrderStatus.PROCESSING, WorkOrderStatus.RESOLVED): WorkOrderAction.RESOLVE,
		(WorkOrderStatus.RESOLVED, WorkOrderStatus.CLOSED): WorkOrderAction.CLOSE,
	}

	return transition_map.get((old_status, new_status), WorkOrderAction.CREATE)


# ---------- 工单分派操作 ----------

async def dispatch_work_order(
		db: DatabaseService,
		work_order_id: int,
		payload: WorkOrderDispatchRequest,
		operator_code: str
) -> bool:
	"""
	分派工单

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID
		payload: 分派数据
		operator_code: 操作人编码

	Returns:
		bool: 分派成功返回True，工单不存在或状态不正确返回False
	"""
	# 检查工单是否存在
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return False

	# 检查工单状态是否为CREATED
	if work_order.status != WorkOrderStatus.CREATED:
		raise BizException.from_error_code(
			ErrorCode.BAD_REQUEST,
			message=f"工单状态不正确，当前状态: {work_order.status.label()}"
		)

	sql = """
        UPDATE wo_work_order 
        SET assignee_code = %s, expect_finish_time = %s, status = %s, updated_at = NOW()
        WHERE id = %s
    """
	try:
		params = (
			payload.assignee_code,
			payload.expect_finish_time,
			WorkOrderStatus.DISPATCHED.value,
			work_order_id
		)
		affected_rows = db.update(sql, params)

		# 记录分派日志
		if affected_rows > 0:
			comment = payload.comment or f"分派给负责人: {payload.assignee_code}"
			await create_work_order_log(
				db, work_order_id, WorkOrderAction.DISPATCH, operator_code,
				WorkOrderStatus.CREATED, WorkOrderStatus.DISPATCHED, comment
			)

		return affected_rows > 0
	except Exception as e:
		logger.error(f"分派工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"分派工单失败: {str(e)}")


# ---------- 工单处理操作 ----------

async def process_work_order(
		db: DatabaseService,
		work_order_id: int,
		payload: WorkOrderProcessRequest,
		operator_code: str
) -> bool:
	"""
	处理工单（接单）

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID
		payload: 处理数据
		operator_code: 操作人编码

	Returns:
		bool: 处理成功返回True，工单不存在或状态不正确返回False
	"""
	# 检查工单是否存在
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return False

	# 检查工单状态是否为DISPATCHED
	if work_order.status != WorkOrderStatus.DISPATCHED:
		raise BizException.from_error_code(
			ErrorCode.BAD_REQUEST,
			message=f"工单状态不正确，当前状态: {work_order.status.label()}"
		)

	sql = "UPDATE wo_work_order SET status = %s, updated_at = NOW() WHERE id = %s"
	try:
		affected_rows = db.update(sql, (WorkOrderStatus.PROCESSING.value, work_order_id))

		# 记录处理日志
		if affected_rows > 0:
			comment = payload.comment or "开始处理工单"
			await create_work_order_log(
				db, work_order_id, WorkOrderAction.PROCESS, operator_code,
				WorkOrderStatus.DISPATCHED, WorkOrderStatus.PROCESSING, comment
			)

		return affected_rows > 0
	except Exception as e:
		logger.error(f"处理工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"处理工单失败: {str(e)}")


async def resolve_work_order(
		db: DatabaseService,
		work_order_id: int,
		payload: WorkOrderResolveRequest,
		operator_code: str
) -> bool:
	"""
	解决工单

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID
		payload: 解决数据
		operator_code: 操作人编码

	Returns:
		bool: 解决成功返回True，工单不存在或状态不正确返回False
	"""
	# 检查工单是否存在
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return False

	# 检查工单状态是否为PROCESSING
	if work_order.status != WorkOrderStatus.PROCESSING:
		raise BizException.from_error_code(
			ErrorCode.BAD_REQUEST,
			message=f"工单状态不正确，当前状态: {work_order.status.label()}"
		)

	sql = """
        UPDATE wo_work_order 
        SET solution = %s, status = %s, updated_at = NOW()
        WHERE id = %s
    """
	try:
		params = (
			payload.solution,
			WorkOrderStatus.RESOLVED.value,
			work_order_id
		)
		affected_rows = db.update(sql, params)

		# 记录解决日志
		if affected_rows > 0:
			comment = payload.comment or "工单已解决"
			await create_work_order_log(
				db, work_order_id, WorkOrderAction.RESOLVE, operator_code,
				WorkOrderStatus.PROCESSING, WorkOrderStatus.RESOLVED, comment
			)

		return affected_rows > 0
	except Exception as e:
		logger.error(f"解决工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"解决工单失败: {str(e)}")


async def close_work_order(
		db: DatabaseService,
		work_order_id: int,
		payload: WorkOrderCloseRequest,
		operator_code: str
) -> bool:
	"""
	关闭工单

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID
		payload: 关闭数据
		operator_code: 操作人编码

	Returns:
		bool: 关闭成功返回True，工单不存在或状态不正确返回False
	"""
	# 检查工单是否存在
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return False

	# 检查工单状态是否为RESOLVED
	if work_order.status != WorkOrderStatus.RESOLVED:
		raise BizException.from_error_code(
			ErrorCode.BAD_REQUEST,
			message=f"工单状态不正确，当前状态: {work_order.status.label()}"
		)

	sql = """
        UPDATE wo_work_order 
        SET close_comment = %s, closed_by = %s, closed_at = NOW(), status = %s, updated_at = NOW()
        WHERE id = %s
    """
	try:
		params = (
			payload.close_comment,
			operator_code,
			WorkOrderStatus.CLOSED.value,
			work_order_id
		)
		affected_rows = db.update(sql, params)

		# 记录关闭日志
		if affected_rows > 0:
			comment = payload.close_comment or "工单已关闭"
			await create_work_order_log(
				db, work_order_id, WorkOrderAction.CLOSE, operator_code,
				WorkOrderStatus.RESOLVED, WorkOrderStatus.CLOSED, comment
			)

		return affected_rows > 0
	except Exception as e:
		logger.error(f"关闭工单失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"关闭工单失败: {str(e)}")


async def create_work_order_log(
		db: DatabaseService,
		wo_id: int,
		action: WorkOrderAction,
		actor_code: str,
		old_status: Optional[WorkOrderStatus],
		new_status: Optional[WorkOrderStatus],
		comment: Optional[str] = None
) -> int:
	"""
	创建工单日志

	Args:
		db: 数据库服务实例
		wo_id: 工单ID
		action: 操作类型
		actor_code: 操作者编码
		old_status: 原状态
		new_status: 新状态
		comment: 备注

	Returns:
		int: 新创建的日志ID
	"""
	# 获取操作者姓名
	actor_name = None
	try:
		user_sql = "SELECT name FROM base_user WHERE user_code = %s"
		user_row = db.get_one(user_sql, (actor_code,))
		if user_row:
			actor_name = user_row["name"]
	except Exception as e:
		logger.warning(f"获取操作者姓名失败: {e}")

	sql = """
        INSERT INTO wo_work_order_log (
            wo_id, action, actor_code, actor_name, comment, old_status, new_status, created_at
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, NOW()
        )
    """
	try:
		params = (
			wo_id,
			action.value,
			actor_code,
			actor_name,
			comment,
			old_status.value if old_status else None,
			new_status.value if new_status else None,
		)
		return db.insert(sql, params)
	except Exception as e:
		logger.error(f"创建工单日志失败: {e}")
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message=f"创建工单日志失败: {str(e)}")


async def get_work_order_logs(db: DatabaseService, wo_id: int) -> List[WorkOrderLog]:
	"""
	获取工单日志列表

	Args:
		db: 数据库服务实例
		wo_id: 工单ID

	Returns:
		List[WorkOrderLog]: 工单日志列表
	"""
	sql = """
        SELECT id, wo_id, action, actor_code, actor_name, comment, old_status, new_status, created_at
        FROM wo_work_order_log
        WHERE wo_id = %s
        ORDER BY created_at ASC
    """
	try:
		rows = db.get_all(sql, (wo_id,))
		return [WorkOrderLog(**row) for row in rows]
	except Exception as e:
		logger.error(f"查询工单日志失败: {e}")
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message=f"查询工单日志失败: {str(e)}")


# ---------- 工单详情操作 ----------

async def get_work_order_detail(db: DatabaseService, work_order_id: int) -> Optional[dict]:
	"""
	获取工单详情（包含日志）

	Args:
		db: 数据库服务实例
		work_order_id: 工单ID

	Returns:
		Optional[dict]: 工单详情，不存在则返回None
	"""
	work_order = await get_work_order_by_id(db, work_order_id)
	if not work_order:
		return None

	logs = await get_work_order_logs(db, work_order_id)

	return {
		"work_order": work_order,
		"logs": logs
	}
